package com.szholly.pro.watergas_server.ps.controller;

import java.io.IOException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.web.BaseController;
import com.szholly.pro.watergas_server.common.web.RequestUnit;
import com.szholly.pro.watergas_server.ps.service.OperationTaskService;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

import net.sf.json.JSONObject;

@Controller
@RequestMapping("/SwTaskQuery/*")
public class SwTaskQuery extends BaseController {
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	OperationTaskService operationTaskService;

	/**
	 * 获取污水厂事件 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getJobList")
	public void getJobList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String job_type= RequestUnit.getParameter("job_type");
		String sql = DataUtil.CACHE + " select t.* from OPERATION_JOB t where 1=1 ";
		
		if (job_type != null && !job_type.equals("")) {
			sql += " and t.job_type='" + job_type + "'";
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String result = DataUtil.getPageSelectJson(sql, parms);
		outString(result);
	}

	/**
	 * 获取运维组 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getOperatorGroupList")
	public void getOperatorGroupList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String waterType= RequestUnit.getParameter("waterType");
		try {
			String sql ="";
			if(waterType.equals("0")){
				sql = DataUtil.CACHE + " select *  from OC_GROUP where px< 8 order by px asc";
			}else if(waterType.equals("3")){
				sql = DataUtil.CACHE + " select *  from OC_GROUP where px in('9','8') order by px asc";
			}
			else if(waterType.equals("1")){
				sql = DataUtil.CACHE + " select *  from OC_GROUP where px in('1','2') order by px asc";
			}
			else{
				sql = DataUtil.CACHE + " select *  from OC_GROUP order by px asc";
			}
			List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
			String result = DataUtil.getPageSelectJson(sql, parms);
			outString(result);
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}

	/**
	 * 获取任务单数据 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getTaskList")
	public void getTaskList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String,String> params = getTaskQueryParams();
		PageInfo pageInfo = DataUtil.getPageInfo();
		List<RowBase> list = operationTaskService.getTaskList(params,pageInfo);
		JSONObject json=DataUtil.listToJSONObject(list,null,pageInfo);
		outString(json.toString());
	}
	/**
	 * 运维记录导出Excel
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="outTaskExcel")
	public void outTaskExcel(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String,String> params = getTaskQueryParams();
		PageInfo pageInfo = DataUtil.getPageInfo();
		pageInfo.setPageSize(10000);
		pageInfo.setPageIndex(1);
		List<RowBase> list = operationTaskService.getTaskList(params,pageInfo);
	
		HashMap<String, Object> data = new HashMap<>();
		data.put("TaskList", list);
		HtmlToExcel(data, "Task.html", "运维记录.xls");
	}
	/**
	 * 运维统计
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="/StatisticsTaskCount")
	public void StatisticsTaskCount(HttpServletRequest resq, HttpServletResponse res) throws Exception{
		Map<String,String> params = new HashMap<>();
		
		HashMap<String,Object> queryobject=RequestUnit.getHashMapParmO();
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");	
		String station_id=(String) queryobject.get("queryObject[STATION_ID]");
		String startDate=(String) queryobject.get("objdata[startDate]");
		String endDate=(String) queryobject.get("objdata[endDate]");
		long day=0;
		//计算天数
		if(StringUtil.IsNotNullOrEmpty(startDate)&&StringUtil.IsNotNullOrEmpty(endDate)){
			long miao=format.parse(endDate).getTime()-format.parse(startDate).getTime();
			day=miao/1000/60/60/24;
		}
		if(StringUtil.IsNotNullOrEmpty(station_id)){
			params.put("station_id", station_id);
		}	
		if(StringUtil.IsNotNullOrEmpty(startDate)){
			params.put("start",startDate);
		}
		if(StringUtil.IsNotNullOrEmpty(endDate)){
			params.put("end",endDate);
		}
		Map<String,Object> dataList=filtration(params,day);
		JSONObject result = JSONObject.fromObject(dataList);
		outString(result.toString());
	}
	/**
	 * 运维统计导出
	 * @param resq
	 * @param res
	 * @throws IOException
	 */
	@RequestMapping(value="TaskexportPDF")
	public void TaskexportPDF(HttpServletRequest resq, HttpServletResponse res) throws IOException{
		try {
			String station_id=RequestUnit.getString("STATION_ID");
			String start=RequestUnit.getString("startDate");
			String end=RequestUnit.getString("endDate");
			HashMap<String, Object> data = new HashMap<>();
			Map<String,String> result=new HashMap<>();
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			long day=0;
			//计算天数
			if(StringUtil.IsNotNullOrEmpty(start)&&StringUtil.IsNotNullOrEmpty(start)){
				long miao=format.parse(end).getTime()-format.parse(start).getTime();
				day=miao/1000/60/60/24;
			}
			if(StringUtil.IsNotNullOrEmpty(start)){
				result.put("start",start);
			}
			if(StringUtil.IsNotNullOrEmpty(end)){
				result.put("end",end);
			}
			if(StringUtil.IsNotNullOrEmpty(station_id)){
				result.put("station_id", station_id);
			}	
			Map<String,Object> dataList=filtration(result,day);//运维统计
			data.put("tasklist", dataList);
			data.put("start", start);
			data.put("end",end);
			outPDF(data, "taskStatistics.html", "运维统计.pdf",false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * 获取运维信息
	 * @return
	 * @throws Exception 
	 */
	public Map<String,Object> filtration(Map<String,String> params,long day) throws Exception{
		List<RowBase> list = operationTaskService.TaskStatistics(params);
		HashMap<String,Object> dataTotal=new HashMap<>();
		HashMap<String,Object> dataList=new HashMap<>();
		NumberFormat nt = NumberFormat.getPercentInstance();
		nt.setMinimumFractionDigits(2);//百分比保留小数点后两位数
		Double QualifiedSum=0.0;
		for(RowBase row:list){			
			if(day<=32){//月统计
				if(Integer.valueOf(row.getSafeString("OPERCOUNT"))>=2){
					QualifiedSum=QualifiedSum+1;					
				}
				dataTotal.put("TIME", "2");	
			}else if(day<=93&&day>=32){//季度统计
				if(Integer.valueOf(row.getSafeString("OPERCOUNT"))>=6){
					QualifiedSum=QualifiedSum+1;					
				}
				dataTotal.put("TIME", "6");		
			}else if(day<=366&&day>=94){//年统计
				if(Integer.valueOf(row.getSafeString("OPERCOUNT"))>=24){
					QualifiedSum=QualifiedSum+1;					
				}
				dataTotal.put("TIME", "24");		
			}
		}
			if(QualifiedSum>0){
				if(day<=32){//月统计
				dataTotal.put("Acceptability",nt.format(QualifiedSum/list.size()));//合格数量/总数量=合格率
				dataTotal.put("RejectRatio",nt.format((list.size()-QualifiedSum)/list.size()));//不合格率			
			}else if(day<=93&&day>=32){//季度统计
				dataTotal.put("Acceptability",nt.format(QualifiedSum/list.size()));//合格率
				dataTotal.put("RejectRatio",nt.format((list.size()-QualifiedSum)/list.size()));//不合格率								
			}else if(day<=366&&day>=365){//年统计
				dataTotal.put("Acceptability",nt.format(QualifiedSum/list.size()));//合格率
				dataTotal.put("RejectRatio",nt.format((list.size()-QualifiedSum)/list.size()));//不合格率				
			}
		}else{
			dataTotal.put("Acceptability","0.00%");//合格率
			dataTotal.put("RejectRatio","100%");//不合格率
		}	
		dataTotal.put("FailSum",Math.floor(list.size()-QualifiedSum));//不合格总数量		
		dataTotal.put("QualifiedSum",Math.floor(QualifiedSum));//合格总数量	
		dataList.put("dataTotal", dataTotal);
		dataList.put("TaskList", list);
		return dataList;
	}
	private Map<String,String> getTaskQueryParams(){
		Map<String,String> params = new HashMap<>();
		
		String wwtp_id = RequestUnit.getParameter("WWTP_ID");
		String water_type = RequestUnit.getParameter("WATER_TYPE");
		String flowtypes = RequestUnit.getParameter("stationstr");
		String station_id = RequestUnit.getParameter("STATION_ID");
		String groupids = RequestUnit.getParameter("groupstrid");
		String jobids = RequestUnit.getParameter("taskstrid");
		String taskstatus = RequestUnit.getParameter("TaskStatusStr");
		String start = RequestUnit.getParameter("start");
		String end = RequestUnit.getParameter("end");
		String stationTypestrid=RequestUnit.getParameter("stationTypestrid");
		
		if(StringUtil.IsNotNullOrEmpty(wwtp_id)){
			params.put("wwtp_id", wwtp_id);
		}
		if(StringUtil.IsNotNullOrEmpty(flowtypes)){
			params.put("flow_types", StringUtil.joinForSQL(flowtypes.split(","), ""));
		}
		if(StringUtil.IsNotNullOrEmpty(station_id)){
			params.put("station_id", station_id);
		}
		if(StringUtil.IsNotNullOrEmpty(water_type)){
			params.put("water_type", water_type);
		}
		if(StringUtil.IsNotNullOrEmpty(groupids)){
			params.put("group_ids", StringUtil.joinForSQL(groupids.split(","), ""));
		}
		if(StringUtil.IsNotNullOrEmpty(jobids)){
			params.put("job_ids", StringUtil.joinForSQL(jobids.split(","), ""));
		}
		if(StringUtil.IsNotNullOrEmpty(taskstatus)){
			params.put("task_status", StringUtil.joinForSQL(taskstatus.split(","), ""));
		}
		if(StringUtil.IsNotNullOrEmpty(start)){
			params.put("start", start);
		}
		if(StringUtil.IsNotNullOrEmpty(end)){
			params.put("end", end);
		}
		if(StringUtil.IsNotNullOrEmpty(stationTypestrid)){
			params.put("stationTypestrids", stationTypestrid);
		}
		/*if(StringUtil.IsNotNullOrEmpty(stationTypestrid)){
			params.put("stationTypestrids", StringUtil.joinForSQL(stationTypestrid.split(","), ""));
		}*/
		return params;
	}

	/**
	 * 获取标准物质核查数据 
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getVeriList")
	public void getVeriList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String WWTP_ID = RequestUnit.getParameter("WWTP_ID");
		String stationstr = RequestUnit.getParameter("stationstr");
		String taskcode = RequestUnit.getParameter("taskcode");
		String st = RequestUnit.getParameter("start");
		String et = RequestUnit.getParameter("end");
		String sql = " SELECT t1.*,t2.name as STATION_NAME,t4.C_USEREALNAME,t5.name as WTTP_NAME FROM OP_STAND_VERI t1 "
				+ "left join BC_SITE_INFO t2 on t1.station_id=t2.exp_no "
				+ " left join OPERATION_TASK t3 on t1.task_code=t3.task_code "
				+ " left join BC_ENTERPRISE_ATTR t5 on t2.wwtp_id=t5.exp_no "
				+ "left join safe_user t4 on t1.SUER_ID=t4.S_USERID WHERE 1=1 ";

		if (WWTP_ID != null && !WWTP_ID.equals("")) {
			sql += " and t2.WWTP_ID='" + WWTP_ID + "'";
		}
		if (taskcode != null && !taskcode.equals("")) {
			sql += " and t1.TASK_CODE like'%" + taskcode + "%'";
		}
		if (stationstr != null && !stationstr.equals("")) {
			if (stationstr.indexOf(",") > 0) {
				String[] strs = stationstr.split(",");
				sql += "and ( ";
				for (int i = 0; i < strs.length; i++) {
					if (i == 0) {
						sql += "  t2.flow_type ='" + strs[i] + "'";
					} else if (i == strs.length - 1) {
						sql += " or t2.flow_type ='" + strs[i] + "')";
					} else {
						sql += " or t2.flow_type ='" + strs[i] + "'";
					}

				}
			} else {
				sql += " and t2.flow_type ='" + stationstr + "'";
			}
		}
		if (st != null && !st.equals("")) {
			sql += " and t1.start_time >= to_date('" + st + "','yyyy-mm-dd') ";
		}
		if (et != null && !et.equals("")) {
			sql += " and t1.end_time<= to_date('" + et + "','yyyy-mm-dd') ";
		}

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String result = DataUtil.getPageSelectJson(sql, parms);
		outString(result);
	}

	/**
	 * 获取标准物质更换数据 （地表水日常校准）
	 * 日常校准
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getSubstanceList")
	public void getSubstanceList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String WWTP_ID = RequestUnit.getParameter("WWTP_ID");
		String STATION_ID = RequestUnit.getParameter("STATION_ID");
		String taskcode = RequestUnit.getParameter("taskcode");
		String stationstr = RequestUnit.getParameter("FLOW_TYPE");
		String st = RequestUnit.getParameter("start");
		String et = RequestUnit.getParameter("end");
		String sql="select t6.name as DEVICE_NAME,t6.device_typenum,t1.*,t3.name as WTTP_NAME,t4.code as STATION_ID,t4.name as STATION_NAME,t5.C_USEREALNAME,t4.WWTP_ID" 
                   +" from OPERATION_DAILY_VERIFICATION t1  left join OPERATION_TASK t2 on t1.task_code=t2.task_code   left join BC_SITE_INFO t4 on t2.station_id=t4.exp_no" 
                   +" left join BC_ENTERPRISE_ATTR t3 on t4.wwtp_id=t3.exp_no "
                   +" left join safe_user t5 on t1.created_by=t5.S_USERID  left join BC_SITE_DEVICE_RPT  t6 on t1.device_num=t6.device_typenum and t1.station_code=t6.monitor_point where 1=1 ";
		if (WWTP_ID != null && !WWTP_ID.equals("")) {
			sql += " and t4.WWTP_ID='" + WWTP_ID + "'";
		}
		if (StringUtil.IsNotNullOrEmpty(STATION_ID)) {
			sql += " and t4.CODE='" + STATION_ID + "'";
		}
		if (taskcode != null && !taskcode.equals("")) {
			sql += " and t1.TASK_CODE like'%" + taskcode + "%'";
		}
		if (stationstr != null && !stationstr.equals("")) {
			if (stationstr.indexOf(",") > 0) {
				String[] strs = stationstr.split(",");
				sql += "and ( ";
				for (int i = 0; i < strs.length; i++) {
					if (i == 0) {
						sql += "  t4.flow_type ='" + strs[i] + "'";
					} else if (i == strs.length - 1) {
						sql += " or t4.flow_type ='" + strs[i] + "')";
					} else {
						sql += " or t4.flow_type ='" + strs[i] + "'";
					}

				}
			} else {
				sql += " and t4.flow_type ='" + stationstr + "'";
			}
		}

		if (st != null && !st.equals("")) {
			sql += " and t1.VERIFY_DATE >= to_date('" + st + "','yyyy-mm-dd') ";
		}
		if (et != null && !et.equals("")) {
			sql += " and t1.VERIFY_DATE <= to_date('" + et + "','yyyy-mm-dd') ";
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String result = DataUtil.getPageSelectJson(sql, parms);
		outString(result);
	}
	
	/**
	 * 获取标准物质更换数据 (地表水导出日常校准)
	 * 日常校准
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/exportSubstanceDoc")
	public void exportSubstanceDoc(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String WWTP_ID = RequestUnit.getParameter("WWTP_ID");
		String STATION_ID = RequestUnit.getParameter("STATION_ID");
		String taskcode = RequestUnit.getParameter("TASK_CODE");
		String stationstr = RequestUnit.getParameter("FLOW_TYPE");
		String st = RequestUnit.getParameter("start");
		String et = RequestUnit.getParameter("end");
		String sql="select t6.name as DEVICE_NAME,t6.device_typenum,t1.*,decode(t1.verify_type,'1','手动'，'0','自动') TYPE,decode(t1.verify_result,'1','不通过','0','通过') result,to_char(t1.created_on,'iw') weekNumber,t3.name as WTTP_NAME,t4.code as STATION_ID,t4.name as STATION_NAME,t5.C_USEREALNAME,t4.WWTP_ID" 
                   +" from OPERATION_DAILY_VERIFICATION t1  left join OPERATION_TASK t2 on t1.task_code=t2.task_code   left join BC_SITE_INFO t4 on t2.station_id=t4.exp_no" 
                   +" left join BC_ENTERPRISE_ATTR t3 on t4.wwtp_id=t3.exp_no "
                   +" left join safe_user t5 on t1.created_by=t5.S_USERID  left join BC_SITE_DEVICE_RPT  t6 on t1.device_num = t6.device_num  and t1.station_code=t6.monitor_point where 1=1 ";
		if (WWTP_ID != null && !WWTP_ID.equals("")) {
			sql += " and t4.WWTP_ID='" + WWTP_ID + "'";
		}
		if (StringUtil.IsNotNullOrEmpty(STATION_ID)) {
			sql += " and t4.CODE='" + STATION_ID + "'";
		}
		if (taskcode != null && !taskcode.equals("")) {
			sql += " and t1.TASK_CODE like'%" + taskcode + "%'";
		}
		if (stationstr != null && !stationstr.equals("")) {
			if (stationstr.indexOf(",") > 0) {
				String[] strs = stationstr.split(",");
				sql += "and ( ";
				for (int i = 0; i < strs.length; i++) {
					if (i == 0) {
						sql += "  t4.flow_type ='" + strs[i] + "'";
					} else if (i == strs.length - 1) {
						sql += " or t4.flow_type ='" + strs[i] + "')";
					} else {
						sql += " or t4.flow_type ='" + strs[i] + "'";
					}

				}
			} else {
				sql += " and t4.flow_type ='" + stationstr + "'";
			}
		}

		if (st != null && !st.equals("")) {
			sql += " and t1.VERIFY_DATE >= to_date('" + st + "','yyyy-mm-dd') ";
		}
		if (et != null && !et.equals("")) {
			sql += " and t1.VERIFY_DATE <= to_date('" + et + "','yyyy-mm-dd') ";
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> list = DataUtil.getSelectRows(sql, parms);
		Map<String, Object> root = new HashMap<String, Object>();
		//统计校准了多少个仪器
		String statisticSql ="select t2.name,t1.device_num,t2.device_typenum from operation_daily_verification t1\n" +
							" left join BC_SITE_DEVICE_RPT  t2 on t1.station_code=t2.monitor_point\n" + 
							"                                   and t1.device_num=t2.device_num"+
							" where 1=1 ";
		
		if (StringUtil.IsNotNullOrEmpty(STATION_ID)) {
			statisticSql += " and t1.station_code='" + STATION_ID + "'";
		}
		if (taskcode != null && !taskcode.equals("")) {
			statisticSql += " and t1.TASK_CODE like'%" + taskcode + "%'";
		}
		statisticSql +="group by t2.name, t1.device_num,t2.device_typenum";
		List<RowBase> deviceList = DataUtil.getSelectRows(statisticSql, parms);
		RowBase row = new RowBase();
		if(list!=null && list.size()>0){
			row = list.get(0);
			root.put("STATION_NAME", row.getSafeString("STATION_NAME"));
			root.put("CREATED_ON", row.getValue("CREATED_ON"));
			root.put("C_USEREALNAME", row.getSafeString("C_USEREALNAME"));
			root.put("WEEKNUMBER", row.getSafeString("WEEKNUMBER"));
		}
		for(int i=0;i<deviceList.size();i++) {
			int replaceNumber=0;
			row = new RowBase();
			List<RowBase> data = new ArrayList<RowBase>();
			row = deviceList.get(i);
			String param = row.getSafeString("DEVICE_NUM");
			for(int j=0;j<list.size();j++) {
				RowBase rowS = new RowBase();
				rowS= list.get(j);
				String paramS = rowS.getSafeString("DEVICE_NUM");
				if(param.equals(paramS)) {
					replaceNumber++;
					data.add(rowS);
				}
			}
			deviceList.get(i).put("calibrationItem",data);
			deviceList.get(i).put("calibrationNumber",(int)Math.ceil((double)replaceNumber/2)==0?0:((int)Math.ceil((double)replaceNumber/2))-1);
			
		}
		root.put("deviceList",deviceList);
		root.put("deviceNumber", (int)Math.ceil((double)deviceList.size()/2)==0?0:((int)Math.ceil((double)deviceList.size()/2))-1);
		outWord(root, "sw/dailyCalibration.xml", "日常校准记录表.doc");
	}
	/**
	 * 获取站点运维组数据 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getStaionGroupList")
	public void getStaionGroupList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String WWTP_ID = RequestUnit.getParameter("WWTP_ID");
		String flowType = RequestUnit.getParameter("flowType");
		String sql = "SELECT T1.NAME,T1.EXP_NO,t3.group_name,t2.guid,t4.group_id,t4.c_userealname,t4.C_USERTEL AS TELEPHONE from BC_SITE_INFO T1 "
				+ "LEFT JOIN operation_group_station_asso t2 on t2.station_id=t1.exp_no left join OC_GROUP t3 on t3.guid=t2.operation_group_id "
				+ "left join (select distinct oga.group_id,su.c_userealname,su.C_USERTEL from OC_GROUP_ASSO oga left join safe_user su on oga.user_id=su.s_userid where oga.group_head='1') t4 on t3.guid=t4.group_id "
				+ "WHERE T1.WWTP_ID='" + WWTP_ID + "' AND T1.FLOW_TYPE='" + flowType + "'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		String result = DataUtil.getPageSelectJson(sql, parms, pTable);
		outString(result);
	}

	/**
	 * 获取异常和缺失数据的标示和记录 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getExceptionList")
	public void getExceptionList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();

		String sql = "select * from (select * from OPERATION_DATA_ABNORMAL t"
				+ " left join (select s.name,s.wwtp_id, s.code,j.s_domaincode as f_type, j.s_domaincaption as flow_type,p.name as wwtp_name"
				+ " from BC_SITE_INFO s  left join (select s_domaincode, s_domaincaption"
				+ " from data_defulatdomain where s_domainname = 'STATION_FLOW_NAME') j on j.s_domaincode=s.flow_type"
				+ " left join sde.Ps_Wwtp p on s.wwtp_id = p.exp_no) ms on t.station_id=ms.code"
				+ " left join (select s_domaincode, s_domaincaption as exception_caption"
				+ " from data_defulatdomain where s_domainname = 'DATA_ANORMAL') k on k.s_domaincode=t.exception_code) fr where 1=1";

		String task_code = RequestUnit.getParameter("taskCode");
		if (StringUtil.IsNotNullOrEmpty(task_code)) {
			sql += " and fr.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", task_code));
		} else {
			String start = RequestUnit.getParameter("start");
			if (StringUtil.IsNotNullOrEmpty(start)) {
				sql += " and fr.start_time >= to_date('" + start + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			String end = RequestUnit.getParameter("end");
			if (StringUtil.IsNotNullOrEmpty(start)) {
				sql += " and fr.start_time <= to_date('" + end + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			String wwtpid = RequestUnit.getParameter("WWTP_ID");
			if (StringUtil.IsNotNullOrEmpty(wwtpid)) {
				sql += " and fr.wwtp_id = @wwtpid";
				listParam.add(new GlobalParameter("wwtpid", wwtpid));
			}
			String flowtype = RequestUnit.getParameter("FLOW_TYPE");
			if (StringUtil.IsNotNullOrEmpty(flowtype)) {
				sql += " and fr.f_type = @flowtype";
				listParam.add(new GlobalParameter("flowtype", flowtype));
			}
		}
		String result = DataUtil.getPageSelectJson(sql, listParam);
		outString(result);
	}

	/**
	 * 人工监测 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getArtificialMonitorList")
	public void getArtificialMonitorList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String st = RequestUnit.getParameter("start");
		String et = RequestUnit.getParameter("end");
		String STATION_NAME = RequestUnit.getParameter("STATION_NAME");
		String sql = "select t2.group_name,t1.* from OPERATION_ARTIFICIAL_MONITOR t1 left join OC_GROUP t2 on t1.monitor_group =t2.guid  where 1=1 ";
		if (STATION_NAME != null && !STATION_NAME.equals("")) {
			sql += " and t1.STATION_NAME like '%" + STATION_NAME + "%'";
		}
		if (st != null && !st.equals("")) {
			sql += " and t1.start_time >= to_date('" + st + "','yyyy-mm-dd') ";
		}
		if (et != null && !et.equals("")) {
			sql += " and t1.end_time<= to_date('" + et + "','yyyy-mm-dd') ";
		}

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		String result = DataUtil.getPageSelectJson(sql, parms,pTable);
		outString(result);
	}

	/**
	 * 所有异常记录 wangl
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getAllAnormalList")
	public void getAllAnormalList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String st = RequestUnit.getParameter("start");
		String et = RequestUnit.getParameter("end");
		String TASK_CODE = RequestUnit.getParameter("TASK_CODE");
		String sql = "select t1.*,t2.c_userealname as CREATE_BY_NAME,t3.c_userealname as SOLVE_BY_NAME  from OPERATION_EXCEPTION t1"
				+ " left join safe_user t2 on t2.s_userid = t1.CREATE_BY"
				+ " left join safe_user t3 on t3.s_userid = t1.solve_by where 1=1 ";
		if (st != null && !st.equals("")) {
			sql += " and t1.CREATE_TIME >= to_date('" + st + "','yyyy-mm-dd') ";
		}
		if (et != null && !et.equals("")) {
			sql += " and t1.SOLVE_TIME<= to_date('" + et + "','yyyy-mm-dd') ";
		}
		if (TASK_CODE != null && !TASK_CODE.equals("")) {
			sql += " and t1.TASK_CODE like'%" + TASK_CODE + "%' ";
		}

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		String result = DataUtil.getPageSelectJson(sql, parms, pTable);
		outString(result);
	}

	/**
	 * 获取备件/耗材更换相关任务数据
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getMaterialTaskList")
	public void getMaterialTaskList(HttpServletRequest resq, HttpServletResponse res) throws Exception {

		String strWWTP_ID = RequestUnit.getParameter("WWTP_ID");
		String strFLOW_TYPE = RequestUnit.getParameter("stationstr");
		String strGROUP_ID = RequestUnit.getParameter("groupstrid");
		// String strTASK_CODE = RequestUnit.getParameter("taskstrid");
		String strTASK_STATUS = RequestUnit.getParameter("TaskStatusStr");
		String strSTART_TIME = RequestUnit.getParameter("start");
		String strEND_TIME = RequestUnit.getParameter("end");

		String[] arrGROUP_ID = strGROUP_ID.split(",");
		// String[] arrTASK_CODE = strTASK_CODE.split(",");
		String[] arrTASK_STATUS = strTASK_STATUS.split(",");

		String strSQL = " select t1.*, t2.STATION_ONOFF,t2.WWTP_ID as PS_WWTP_ID,t2.name as STATION_NAME, t3.JOB_NAME, t4.GROUP_NAME, \n"
				+ " (case t1.IS_UNUSUAL when '1' then '有|查看异常记录' else '无' end) as IS_UNUSUAL_DESC, t5.name as WWTP_NAME  \n"
				+ "	from  ( \n" + " 	select t.TASK_CODE \n" + "		from OP_TASK  t \n"
				+ "		inner join OP_MATE_REPLACE t1 on t1.TASK_ID = t.TASK_CODE \n"
				+ "		where 1=1 \n";

		List<String> stationList = getWwtpStationIdList(strWWTP_ID, strFLOW_TYPE);

		if (stationList.size() > 0) {
			strSQL = strSQL + " AND ( ";

			for (int i = 0; i < stationList.size(); i++) {
				if (i < stationList.size() - 1)
					strSQL = strSQL + "t.STATION_ID = '" + stationList.get(i) + "' OR ";
				else
					strSQL = strSQL + "t.STATION_ID = '" + stationList.get(i) + "' ";
			}

			strSQL = strSQL + " ) \n";
		}

		if (strGROUP_ID.length() > 0 && arrGROUP_ID.length > 0) {
			strSQL = strSQL + " AND ( ";

			for (int i = 0; i < arrGROUP_ID.length; i++) {
				if (i < arrGROUP_ID.length - 1)
					strSQL = strSQL + "t.OPERATION_GROUP_ID = '" + arrGROUP_ID[i] + "' OR ";
				else
					strSQL = strSQL + "t.OPERATION_GROUP_ID = '" + arrGROUP_ID[i] + "' ";
			}

			strSQL = strSQL + " ) \n";
		}

		if (strTASK_STATUS.length() > 0 && arrTASK_STATUS.length > 0) {
			strSQL = strSQL + " AND ( ";

			for (int i = 0; i < arrTASK_STATUS.length; i++) {
				if (i < arrTASK_STATUS.length - 1)
					strSQL = strSQL + "t.TASK_STATUS = '" + arrTASK_STATUS[i] + "' OR ";
				else
					strSQL = strSQL + "t.TASK_STATUS = '" + arrTASK_STATUS[i] + "' ";
			}

			strSQL = strSQL + " ) \n";
		}

		if (strSTART_TIME != null && strSTART_TIME.length() > 0) {
			strSQL = strSQL + " AND t.START_TIME >= to_date('" + strSTART_TIME
					+ " 00:00:00', 'yyyy-mm-dd hh24:mi:ss')  \n";
		}

		if (strEND_TIME != null && strEND_TIME.length() > 0) {
			strSQL = strSQL + "AND t.START_TIME <= to_date('" + strEND_TIME
					+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')  \n";
		}

		strSQL += "		group by t.TASK_CODE \n" + "	 ) task_code"
				+ " left join OPERATION_TASK t1 on t1.TASK_CODE = task_code.TASK_CODE \n"
				+ " left join BC_SITE_INFO t2 on t2.EXP_NO = t1.STATION_ID \n"
				+ " left join OPERATION_JOB t3 on t3.GUID = t1.JOB_ID \n"
				+ " left join OC_GROUP t4 on t4.GUID = t1.OPERATION_GROUP_ID \n"
				+ " left join BC_ENTERPRISE_ATTR t5 on t5.EXP_NO = t2.WWTP_ID \n";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		String result = DataUtil.getPageSelectJson(strSQL, null, pTable);
		outString(result);
	}

	/**
	 * 获取污水厂的站点列表
	 * 
	 * @param strWwtpID
	 *            - 污水厂编号
	 * @param strFlowType
	 *            - 站点流向 (0/进水口， 1/出水口)
	 * @return - 站点列表
	 */
	public List<String> getWwtpStationIdList(String strWwtpID, String strFlowType) throws Exception {

		String[] arrWWTP_ID = strWwtpID.split(",");
		String[] arrFLOW_TYPE = strFlowType.split(",");

		String strSQL = " select * from BC_SITE_INFO where 1=1 ";

		if (strWwtpID.length() > 0 && arrWWTP_ID.length > 0) {
			strSQL = strSQL + " AND ( ";

			for (int i = 0; i < arrWWTP_ID.length; i++) {
				if (i < arrWWTP_ID.length - 1)
					strSQL = strSQL + "WWTP_ID = '" + arrWWTP_ID[i] + "' OR ";
				else
					strSQL = strSQL + "WWTP_ID = '" + arrWWTP_ID[i] + "' ";
			}

			strSQL = strSQL + " )";
		}

		if (strFlowType.length() > 0 && arrFLOW_TYPE.length > 0) {
			strSQL = strSQL + " AND ( ";

			for (int i = 0; i < arrFLOW_TYPE.length; i++) {
				if (i < arrFLOW_TYPE.length - 1)
					strSQL = strSQL + "FLOW_TYPE = '" + arrFLOW_TYPE[i] + "' OR ";
				else
					strSQL = strSQL + "FLOW_TYPE = '" + arrFLOW_TYPE[i] + "' ";
			}

			strSQL = strSQL + " )";
		}

		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, null);
		if (rcdList.size() < 1)
			return null;

		List<String> stationList = new ArrayList<String>();
		for (int i = 0; i < rcdList.size(); i++) {
			stationList.add(rcdList.get(i).getSafeString("EXP_NO"));
		}

		return stationList;
	}
	/**
	 *运维统计导出
	 * @param resq
	 * @param res
	 * @throws IOException
	 */
	@RequestMapping(value="StatisticsTaskportWord")
	public void StatisticsTaskportWord(HttpServletRequest resq, HttpServletResponse res) throws IOException{
		try {		
			Map<String,String> params = new HashMap<>();
			HashMap<String, Object> data = new HashMap<>();
			String station_id=RequestUnit.getString("STATION_ID");
			String start=RequestUnit.getString("start");
			String end=RequestUnit.getString("end");
			if(StringUtil.IsNotNullOrEmpty(station_id)){
				params.put("station_id", station_id);
			}	
			if(StringUtil.IsNotNullOrEmpty(start)){
				params.put("start",start);
			}
			if(StringUtil.IsNotNullOrEmpty(end)){
				params.put("end",end);
			}
			List<RowBase> list = operationTaskService.TaskStatistics(params);
			data.put("checklist", list);
			outWord(data, "station_check.xml", "运维统计.doc");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * 获取运维任务
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getOperationTaskList")
	public void getOperationTaskList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String guid=RequestUnit.getString("OPERATION_GROUP_ID");
		String JOB_ID=RequestUnit.getString("JOB_ID");
		String sql ="select t2.name,t3.group_name,t4.c_userealname,t5.job_name,t1.* from OP_TASK t1 "
				+" left join BC_SITE_INFO t2 on t2.code=t1.station_id"
				+" left join OC_GROUP t3 on t3.guid=t1.operation_group_id"
				+" left join safe_user t4 on t4.s_userid=t1.finish_by"
				+" left join operation_job t5 on t5.guid=t1.job_id where 1=1  and t2.station_category='3'";
		if(StringUtil.IsNotNullOrEmpty(guid)){
			sql+=" t.operation_group_id='"+guid+"'";
		}
		if(StringUtil.IsNotNullOrEmpty(JOB_ID)){
			sql+=" t.job_id='"+JOB_ID+"'";
		}
		sql+=" order by t1.start_time desc";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("OP_TASK");
		String result = DataUtil.getPageSelectJson(sql, parms, pTable);
		outString(result);
	}
	/**
	 * 获取运维人员名单
	 * 
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value = "/getOperationCrew")
	public void getOperationCrew(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String GROUPID=RequestUnit.getString("OPERATION_GROUP_ID");
		String sql ="select case t1.group_head when '1' then t2.c_userealname || '(组长)' else t2.c_userealname end as username, t1.*"
				+" from OC_GROUP_ASSO t1 left join safe_user t2 on t2.s_userid = t1.user_id where 1=1";
		if(StringUtil.IsNotNullOrEmpty(GROUPID)){
			sql+=" and t1.group_id='"+GROUPID+"'";
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("OC_GROUP_ASSO");
		String result = DataUtil.getPageSelectJson(sql, parms, pTable);
		outString(result);
	}
}
